#!/bin/bash

###########################################################################################################
## 代码执行顺序：hive_all_data.sql --> hive_create_table.sh --> export_table.sql --> mysql.sql --> sqoop.sh ##
###########################################################################################################

# 计算各个表的置信度，由于word与其他表结构不同，begin为3
# hql中单独添加two_words_confidence的代码

list=("" "" "two" "three" "four" "five" "six")

begin=3
loop=6

rm -f ~/confidence.hql

hql="
use productdescriptionanalysis_all;
set hive.exec.mode.local.auto = true;
set hive.exec.mode.local.auto.inputbytes.max = 5368709120;
set hive.exec.mode.local.auto.input.files.max = 20;
set hive.auto.convert.join=false;
set hive.exec.reducers.bytes.per.reducer = 5000000000;
set hive.strict.checks.cartesian.product = false;

drop table if exists two_words_confidence;
create table if not exists two_words_confidence
as
select w.words,
       w.count,
       w.word_first,
       w.count / w1.count word_first_conf,
       w.word_second,
       w.count / w2.count word_second_conf
from (
         select words, substr(words, 1, 1) word_first, substr(words, 2, 1) word_second, count
         from two_words
     ) w
         join word w1 on w.word_first = w1.word
         join word w2 on w.word_second = w2.word;

"

for i in $(seq $begin $loop); do
  confidence="
drop table if exists ${list[i]}_words_confidence;
create table if not exists ${list[i]}_words_confidence
as
select w.words, w.count,
       w.word_first, w.count/w1.count word_first_conf, w.word_second, w.count/w2.count word_second_conf
from
    (
        select words, count,
               substr(words, 1, $((i-1))) word_first, substr(words, 2, $((i-1))) word_second
        from ${list[i]}_words_count
    ) w
        join ${list[i-1]}_words w1 on w.word_first=w1.words
        join ${list[i-1]}_words w2 on w.word_second=w2.words;
"
  hql=${hql}${confidence}
done

hql=${hql}
echo $hql >> ~/confidence.hql

hive -f ~/confidence.hql